﻿namespace DbWorker.AccessModels
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SQLite;
    using DbWorker.DataModels;

    using NLog;

    /// <summary>
    /// Access model to the rules table.
    /// </summary>
    public class RuleAm
    {
        private readonly SQLiteConnection _connection;
        private readonly Logger _logger = LogManager.GetCurrentClassLogger();

        public RuleAm(SQLiteConnection connection)
        {
            _connection = connection;
        }

        /// <summary>
        /// Gets all records rules.
        /// </summary>
        public bool SelectAll(out List<RuleDm> ruleDms)
        {
            ruleDms = new List<RuleDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM folder" + "_rule;", _connection);
                using (command)
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ruleDms.Add(new RuleDm
                            {
                                Id = (long)reader["id"],
                                ParentId = (reader["parent_id"] == DBNull.Value) ? -1 : (long)reader["parent_id"],
                                Title = (string)reader["title"],
                                DirectoryPath = (string)reader["folder_path"],
                                Action = (RuleAction)reader["folder_action"],
                                Addresses = (reader["addresses"] == DBNull.Value) ? string.Empty : (string)reader["addresses"],
                                FileTypes = (reader["file_types"] == DBNull.Value) ? string.Empty : (string)reader["file_types"],
                                Subject = (reader["subject"] == DBNull.Value) ? string.Empty : (string)reader["subject"],
                                Replace = (reader["replace"] == DBNull.Value) ? ReplaceFile.Unknown : (ReplaceFile)reader["replace"],
                                MessageText = (reader["message_text"] == DBNull.Value) ? string.Empty : (string)reader["message_text"],
                                SaveMessage = reader["save_message"] != DBNull.Value && (bool)reader["save_message"],
                                MessageFromFile = reader["message_from_file"] != DBNull.Value && (bool)reader["message_from_file"]
                            });

                            //object x = reader["folder_action"];
                            //string aaa = x.GetType().Name;
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Gets the top-level rules.
        /// </summary>
        public bool SelectMainRules(out List<RuleDm> ruleDms)
        {
            ruleDms = new List<RuleDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM folder_rule WHERE parent_id IS NULL;", _connection);
                using (command)
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ruleDms.Add(new RuleDm
                            {
                                Id = (long)reader["id"],
                                ParentId = -1,
                                Title = (string)reader["title"],
                                DirectoryPath = (string)reader["folder_path"],
                                Action = (RuleAction)reader["folder_action"],
                                Addresses = (reader["addresses"] == DBNull.Value) ? string.Empty : (string)reader["addresses"],
                                FileTypes = (reader["file_types"] == DBNull.Value) ? string.Empty : (string)reader["file_types"],
                                Subject = (reader["subject"] == DBNull.Value) ? string.Empty : (string)reader["subject"],
                                Replace = (reader["replace"] == DBNull.Value) ? ReplaceFile.Unknown : (ReplaceFile)reader["replace"],
                                MessageText = (reader["message_text"] == DBNull.Value) ? string.Empty : (string)reader["message_text"],
                                SaveMessage = reader["save_message"] != DBNull.Value && (bool)reader["save_message"],
                                MessageFromFile = reader["message_from_file"] != DBNull.Value && (bool)reader["message_from_file"]
                            });
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Gets the subrules.
        /// </summary>
        public bool SelectSubrules(long parentId, out List<RuleDm> ruleDms)
        {
            ruleDms = new List<RuleDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM folder_rule WHERE parent_id = @parentId;", _connection);
                using (command)
                {
                    command.Parameters.AddWithValue("@parentId", parentId);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ruleDms.Add(new RuleDm
                            {
                                Id = (long)reader["id"],
                                ParentId = parentId,
                                Title = (string)reader["title"],
                                DirectoryPath = (string)reader["folder_path"],
                                Action = (RuleAction)reader["folder_action"],
                                Addresses = (reader["addresses"] == DBNull.Value) ? string.Empty : (string)reader["addresses"],
                                FileTypes = (reader["file_types"] == DBNull.Value) ? string.Empty : (string)reader["file_types"],
                                Subject = (reader["subject"] == DBNull.Value) ? string.Empty : (string)reader["subject"],
                                Replace = (reader["replace"] == DBNull.Value) ? ReplaceFile.Unknown : (ReplaceFile)reader["replace"],
                                MessageText = (reader["message_text"] == DBNull.Value) ? string.Empty : (string)reader["message_text"],
                                SaveMessage = reader["save_message"] != DBNull.Value && (bool)reader["save_message"],
                                MessageFromFile = reader["message_from_file"] != DBNull.Value && (bool)reader["message_from_file"]
                            });
                        }
                    }
                }
                
                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Adds a new rule to the database.
        /// </summary>
        public bool Insert(RuleDm ruleDm, out long ruleId)
        {
            ruleId = -1;
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("INSERT INTO folder_rule "
                    + "(parent_id, title, folder_path, folder_action, "
                    + "addresses, file_types, subject, replace, message_text, "
                    + "save_message, message_from_file) VALUES "
                    + "(@parent_id, @title, @folder_path, @folder_action, "
                    + "@addresses, @file_types, @subject, @replace, @message_text, "
                    + "@save_message, @message_from_file); "
                    + "SELECT last_insert_rowid();", _connection);

                using (command)
                {
                    if (ruleDm.ParentId <= 0)
                    {
                        command.Parameters.AddWithValue("@parent_id", DBNull.Value);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@parent_id", ruleDm.ParentId);
                    }

                    command.Parameters.AddWithValue("@title", ruleDm.Title);
                    command.Parameters.AddWithValue("@folder_path", ruleDm.DirectoryPath);
                    command.Parameters.AddWithValue("@folder_action", (int)ruleDm.Action);
                    command.Parameters.AddWithValue("@addresses", ruleDm.Addresses);
                    command.Parameters.AddWithValue("@file_types", ruleDm.FileTypes);
                    command.Parameters.AddWithValue("@subject", ruleDm.Subject);
                    command.Parameters.AddWithValue("@replace", (int)ruleDm.Replace);
                    command.Parameters.AddWithValue("@message_text", ruleDm.MessageText);
                    command.Parameters.AddWithValue("@save_message", ruleDm.SaveMessage);
                    command.Parameters.AddWithValue("@message_from_file", ruleDm.MessageFromFile);

                    ruleId = (long)command.ExecuteScalar();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Changes rule in database.
        /// </summary>
        public bool Update(RuleDm ruleDm)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("UPDATE folder_rule SET "
                    + "parent_id = @parent_id, title = @title, folder_path = @folder_path, " +
                    "folder_action = @folder_action, addresses = @addresses, file_types = @file_types, "
                    + "subject = @subject, replace = @replace, message_text = @message_text, "
                    + "save_message = @save_message, message_from_file = @message_from_file "
                    + " WHERE id = @id;", _connection);

                using (command)
                {
                    if (ruleDm.ParentId <= 0)
                    {
                        command.Parameters.AddWithValue("@parent_id", DBNull.Value);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@parent_id", ruleDm.ParentId);
                    }

                    command.Parameters.AddWithValue("@title", ruleDm.Title);
                    command.Parameters.AddWithValue("@folder_path", ruleDm.DirectoryPath);
                    command.Parameters.AddWithValue("@folder_action", (int)ruleDm.Action);
                    command.Parameters.AddWithValue("@addresses", ruleDm.Addresses);
                    command.Parameters.AddWithValue("@file_types", ruleDm.FileTypes);
                    command.Parameters.AddWithValue("@subject", ruleDm.Subject);
                    command.Parameters.AddWithValue("@replace", (int)ruleDm.Replace);
                    command.Parameters.AddWithValue("@message_text", ruleDm.MessageText);
                    command.Parameters.AddWithValue("@save_message", ruleDm.SaveMessage);
                    command.Parameters.AddWithValue("@message_from_file", ruleDm.MessageFromFile);
                    command.Parameters.AddWithValue("@id", ruleDm.Id);

                    command.ExecuteNonQuery();
                }
                
                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Deletes rule from the database.
        /// </summary>
        public bool Delete(long letterId)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;
                
                var command = new SQLiteCommand("DELETE FROM folder_rule WHERE id = @id;", _connection);
                using (command)
                {
                    command.Parameters.AddWithValue("@id", letterId);
                    command.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);
                return false;
            }
        }
    }
}
